﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Numeric;

namespace _0812159_0812164.dao
{
    public class LocationDAO
    {
        public static bool InsertInfo(Location loca)
        {
            bool rs = false;
            DataHelper helper = new DataHelper();
            helper.open();
            String sql = String.Format("INSERT INTO LOCATION(username, name, address,idcategory, latitude,longitude,description) VALUES (_utf8'{0}',_utf8'{1}',_utf8'{2}',{3},{4},{5},_utf8'{6}')", loca.Username, loca.Name, loca.Address,loca.Idcategory, loca.Latitude, loca.Longitude, loca.Description);
            rs = helper.executeUpdate(sql);
            helper.close();
            return rs;
        }

        public static bool UpdateInfo(Location loca)
        {
            bool rs = false;
            DataHelper helper = new DataHelper();
            helper.open();
            String sql = String.Format("UPDATE LOCATION SET name = _utf8'{0}', address= _utf8'{1}', idcategory={2},description=_utf8'{3}' WHERE idlocation = {4}", loca.Name, loca.Address, loca.Idcategory, loca.Description, loca.Idlocation);
            rs = helper.executeUpdate(sql);
            helper.close();
            return rs;
        }

        public static bool DeleteInfo(int id)
        {
            bool rs = false;
            DataHelper helper = new DataHelper();

            String sql = String.Format("DELETE FROM LOCATION WHERE idlocation = {0}", id);
            helper.open();
            rs = helper.executeUpdate(sql);
            helper.close();
            return rs;
        }

        public static List<Location> getListLocation(string username)
        {
            List<Location> list = new List<Location>();
            DataHelper helper = new DataHelper();
            helper.open();
            string sql = "SELECT * FROM LOCATION WHERE username=_utf8'" + username + "'";
            MySqlDataReader reader = helper.executeQuery(sql);
            while (reader.Read())
            {
                Location l = new Location();
                l.Idlocation = (int)reader["idlocation"];
                l.Username = (String)reader["username"];
                l.Name = (String)reader["name"];
                if(!reader.IsDBNull(3))
                    l.Address = (String)reader["address"];
                l.Idcategory = (int)reader["idcategory"];
                l.Latitude = reader.GetFloat("latitude");
                l.Longitude = reader.GetFloat("longitude");
                if(!reader.IsDBNull(7))
                    l.Description = (String)reader["description"];
                list.Add(l);
            }
            helper.close();

            return list;
        }

        public static Location getInfo(int id)
        {
            List<Location> list = new List<Location>();
            DataHelper helper = new DataHelper();
            helper.open();
            string sql = String.Format("SELECT * FROM LOCATION WHERE idlocation={0}",id);
            MySqlDataReader reader = helper.executeQuery(sql);
            while (reader.Read())
            {
                Location l = new Location();
                l.Idlocation = (int)reader["idlocation"];
                l.Username = (String)reader["username"];
                l.Name = (String)reader["name"];
                if (!reader.IsDBNull(3))
                    l.Address = (String)reader["address"];
                l.Idcategory = (int)reader["idcategory"];
                l.Latitude = reader.GetFloat("latitude");
                l.Longitude = reader.GetFloat("longitude");
                if (!reader.IsDBNull(7))
                    l.Description = (String)reader["description"];
                list.Add(l);
                break;
            }
            helper.close();

            return list[0];
        }
        public static List<Location> getListLocationChild(string username,int categoryId)
        {
            List<Location> listLocation = getListLocation(username);
            return getListLocationChild(categoryId, listLocation);
        }

        public static List<Location> getListLocationChild(int categoryId, List<Location> inListLocation)
        {
            List<Location> lResult = new List<Location>();
            foreach (Location cate in inListLocation)
            {
                if (cate.Idcategory == categoryId)
                    lResult.Add(cate);
            }

            return lResult;
        }

        public static List<Location> search(string username, string keyword, int idCatelogy, float lat, float lng, int numOfResults)
        {
            keyword.Replace(' ', '%');
            List<Location> list = new List<Location>();
            DataHelper helper = new DataHelper();
            string sql;
            helper.open();
            if(idCatelogy!=-1)
                sql = String.Format("SELECT * FROM LOCATION WHERE username=_utf8'{0}' AND idcategory='{1}' AND name like _utf8'%{2}%'", username, idCatelogy, keyword);
                
            else
                sql = String.Format("SELECT * FROM LOCATION WHERE username=_utf8'{0}' AND name like _utf8'%{1}%'", username, keyword);

            MySqlDataReader reader = helper.executeQuery(sql);
            while (reader.Read())
            {
                Location l = new Location();
                l.Idlocation = (int)reader["idlocation"];
                l.Username = (String)reader["username"];
                l.Name = (String)reader["name"];
                if (!reader.IsDBNull(3))
                    l.Address = (String)reader["address"];
                l.Idcategory = (int)reader["idcategory"];
                l.Latitude = reader.GetFloat("latitude");
                l.Longitude = reader.GetFloat("longitude");
                if (!reader.IsDBNull(7))
                    l.Description = (String)reader["description"];
                list.Add(l);
            }
            helper.close();
            calShortest(list, numOfResults,lat,lng);
            return list;
        }

        private static List<Location> calShortest(List<Location> lBase, int num, float lat, float lng)
        {
            int min = num < lBase.Count ? num :  lBase.Count;
            for (int i = 0; i < min; i++)
                for (int j = 0; j < lBase.Count; j++ )
                {
                    if (Distance(lBase[i], lat, lng) > Distance(lBase[j], lat, lng))
                        Swap(ref lBase, i, j);
                }

            lBase.ToString();
            return lBase;
        }

        private static float Distance(Location lo, float x2, float y2)
        {
            return (float) Math.Sqrt(Math.Pow((lo.Latitude - x2), 2) + Math.Pow((lo.Longitude - y2), 2));
        }

        private static void Swap(ref List<Location> lBase, int x, int y)
        {   
            Location[] lo;
            lo = lBase.ToArray();
            Location temp = new Location();
            temp.Copy(lBase[x]);
            lBase[x].Copy(lo[y]);
            lBase[y].Copy(temp);

            lBase.Clear();
            lBase.AddRange(lo);
        }

        
    }
}
